package org.kukker;

import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

import org.kukker.event.Event;
import org.kukker.upcoming.UpcomingEvent;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBHelper {
    class Row extends Object {
        public String body;
        public long rowId;
        public String title;
    }

    private static final String CREATE_TABLE_EVENTS =
        "create table events (" +
        "	rowId INTEGER PRIMARY KEY AUTOINCREMENT,"+
        "	remoteId STRING DEFAULT 0," +
        "	originId STRING DEFAULT 0," +
        
        "	typeId INTEGER," +
        
        "	name TEXT NOT NULL," +
        "	description TEXT NOT NULL," +
                
        "	startDate TEXT NOT NULL," +
        "	endDate TEXT NOT NULL," +
        
        "	startTime TEXT NOT NULL," +
        "	endTime TEXT NOT NULL," +
        
        "	venueName TEXT NOT NULL," +
        "	venueAddress TEXT NOT NULL," +
        "	venueCity TEXT NOT NULL," +
        "	venueCountry TEXT NOT NULL, " +
        
        "	longitude TXT NOT NULL," +
        "	latitude TXT NOT NULL," +
        
       "	url TEXT NOT NULL " +
        
        "); ";
        
        private static final String CREATE_TABLE_ORIGINS =
        "create table origins (" +
        "	originId INTEGER primary key autoincrement," +
        "	name TEXT NOT NULL," +
        " 	namespace TEXT NOT NULL" +
        ");";
      
        private static final String INITIALIZE_TABLE_VALUES =
        "insert into origins(name,namespace) values ('local','local');"+
        "insert into origins(name,namespace) values ('Upcoming.org','http://www.upcoming.org');"
       ;

    private static final String DATABASE_NAME = "kukker.db";

    private static final String DATABASE_TABLE = "events";
    private static final String TABLE_EVENTS = "events";
    private static final String TABLE_ORIGINS = "origins";

    private static final int DATABASE_VERSION = 1;

    private SQLiteDatabase db;

    public DBHelper(Context ctx) {
        try {
            db = ctx.openDatabase(DATABASE_NAME, null);
        } catch (FileNotFoundException e) {
            try {            	
                db = ctx.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0,                     null);
                db.execSQL(CREATE_TABLE_EVENTS);
                db.execSQL(CREATE_TABLE_ORIGINS);
                db.execSQL(INITIALIZE_TABLE_VALUES);
            } catch (FileNotFoundException e1) {
                db = null;
            }
        }
    }

    public void close() {
        db.close();
    }

    public void createRow(String title, String body) {
        ContentValues initialValues = new ContentValues();
        initialValues.put("title", title);
        initialValues.put("body", body);
        db.insert(DATABASE_TABLE, null, initialValues);
    }

    public void deleteRow(long rowId) {
        db.delete(DATABASE_TABLE, "rowid=" + rowId, null);
    }

    public List<Row> fetchAllRows() {
    	Log.i("db","fetchAllRows");
        ArrayList<Row> ret = new ArrayList<Row>();
        try {
            Cursor c =
                db.query(DATABASE_TABLE, new String[] {
                    "rowid", "title", "body"}, null, null, null, null, null);
            int numRows = c.count();
            c.first();
            for (int i = 0; i < numRows; ++i) {
            
                Row row = new Row();
                row.rowId = c.getLong(0);
                row.title = c.getString(1);
                row.body = c.getString(2);
                ret.add(row);
            	Log.i("db","fetchAllRows(), found: "+row.title);
                c.next();
            }
        } catch (SQLException e) {
            Log.e("booga", e.toString());
        }
        return ret;
    }
    

    /***
     * Fetches all saved events in database
     * @return
     */
    public Event[] fetchAllEvents() {
        Event ret[] = null;
        try {
            Cursor c =
                db.query(DATABASE_TABLE, new String[]     {            		
                    "rowId",
                    "remoteId",
                    "name",
                    "description",
                    "startDate",
                    "endDate",
                    "startTime",
                    "endTime",
                    "venueName",
                    "venueAddress",
                    "venueCity",
                    "venueCountry",
                    "url",
                    "typeId",
                    "originId"
                },
                    
                    null, null, null, null, "startDate ASC");
            int numRows = c.count();
            
            Log.i("db","fetchAllEvents(), found: "+numRows);
            
            ret = new Event[numRows];
            c.first();
            for (int i = 0; i < numRows; ++i) {
            	
                ret[i] = new Event();
                
                int j = 0;      
                ret[i].id = c.getInt(j++);
                ret[i].remoteId = Integer.parseInt(c.getString(j++));
                ret[i].name = c.getString(j++);
                ret[i].description = c.getString(j++);
                ret[i].startDate = c.getString(j++);
                ret[i].endDate = c.getString(j++);
                ret[i].startTime = c.getString(j++);
                ret[i].endTime = c.getString(j++);                
                ret[i].venueName = c.getString(j++);
                ret[i].venueAddress = c.getString(j++);
                ret[i].venueCity = c.getString(j++);
                ret[i].venueCountry = c.getString(j++);
                ret[i].url = c.getString(j++);
                ret[i].typeId = c.getInt(j++);
                ret[i].originId = c.getInt(j++);
                
                ret[i].setIsSaved(true);
                
                //instantiate different class based on originId
                if(ret[i].originId == 1){
                	ret[i] = new UpcomingEvent(ret[i]);
                }
                
                c.next();
            }
            
        } catch (SQLException e) {
            Log.e("error", e.toString());
        }
        return ret;
    }

    public Row fetchRow(long rowId) {
        Row row = new Row();
        Cursor c =
            db.query(true, DATABASE_TABLE, new String[] {
                "rowid", "title", "body"}, "rowid=" + rowId, null, null,
                null, null);
        if (c.count() > 0) {
            c.first();
            row.rowId = c.getLong(0);
            row.title = c.getString(1);
            row.body = c.getString(2);
            return row;
        } else {
            row.rowId = -1;
            row.body = row.title = null;
        }
        return row;
    }

    public void updateRow(long rowId, String title, String body) {
        ContentValues args = new ContentValues();
        args.put("title", title);
        args.put("body", body);
        db.update(DATABASE_TABLE, args, "rowid=" + rowId, null);
    }
    
    public void updateEvent(Event event){
    	ContentValues args = event.getContentValues();    	
    	Log.i("db","updateEvent(event)");
    	db.update(TABLE_EVENTS, args, "rowId=" +event.id,null);
    }
   
    public  long insertEvent(Event event, int originId){    	
    	ContentValues cv = event.getContentValues();
    	cv.put("originId", originId);
    	long rowId = db.insert(TABLE_EVENTS,null, cv);    	
    	Log.i("db","insertEvent(event,originid):  new rowId:"+rowId);
    	return rowId;
    }
    
    public void insertEvent(Event event){
    	ContentValues cv = event.getContentValues();
        long rowId = db.insert(TABLE_EVENTS,null, cv);
        event.setIsSaved(true);
        Log.i("db","insertEvent(Event event), remoteid: "+event.remoteId+", newRowId: "+rowId);
    }
    

    
    public Event fecthEventUsingRemoteId(long remoteId) {
        Event row = new Event();
        Cursor c =
            db.query(true, TABLE_EVENTS, 
            		new String[] {"rowId"}, "remoteId=" + remoteId, 
            		null, null,	null, null);        
        if (c.count() > 0) {
            c.first();
            row.remoteId = c.getInt(0);           
            return row;
        } else {
        	
            //row.remoteId = -1;            
        }
        
        return null;
    }

    
    public int deleteEventUsingRemoteId(long remoteId) {                
        int affectedRow = db.delete(TABLE_EVENTS, "remoteId="+remoteId,null);
        Log.i("db",this.getClass().getName()+" deleteEventUsingRemoteId, remoteid:"+remoteId+" affected:"+affectedRow);
        return affectedRow;        
    }
   
    public int deleteEvent(long rowId) {                
        int affectedRow = db.delete(TABLE_EVENTS, "rowId="+rowId,null);
        //Log.i("db",this.getClass().getName()+" deleteEventUsingRemoteId, remoteid:"+remoteId+" affected:"+affectedRow);
        return affectedRow;        
    }
   
    
    
    private void log(String method,String text,String text2){
    	String tag = "db";
    	
    	
    	Log.i(tag,
    			"["+this.getClass().getSimpleName()+"]" +
    			
    			"["+text+"]"
    			
    			+"["
    			+method+", "+text2		
    			+"]"
    			
    	);
    	
    }
    
    
    
}


